--***********************************************************
--SELECT Stored Procedure for <Table> table
--***********************************************************
GO

USE <Database>
GO


IF EXISTS (SELECT sys.objects.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.name = '<TableU>Select'  and sys.schemas.name = '<Owner>'  and sys.objects.type = 'P') 
DROP PROCEDURE [<Owner>].[<TableU>Select] 
GO

CREATE PROCEDURE [<Owner>].[<TableU>Select] 
<If Loop>
   <IsPKey>
     <SF[,]>@<Field> <SQLType>
</If Loop>
<If Loop>
   <IsNotPKey>
   <And>
   <IsIdentity>
     @<Field> <SQLType>
</If Loop>
<If Loop>
   <IsNotIdentityCount>
   <And>
   <IsNotPKeyCount>
   <And>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
     @<Field> <SQLType>
   <ExitLoop>
</If Loop>
AS 
BEGIN 
SELECT 
<Loop>
     <SF[,]>[<Field>]
</Loop>
FROM
     [<Owner>].[<Table>]
WHERE
<If Loop>
   <IsPKey>
<SF[AND ]>[<Field>] = @<Field>
</If Loop>
<If Loop>
   <IsNotPKey>
   <And>
   <IsIdentity>
     [<Field>] = @<Field>
</If Loop>
<If Loop>
   <IsNotIdentityCount>
   <And>
   <IsNotPKeyCount>
   <And>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
     [<Field>] = @<Field>
   <ExitLoop>
</If Loop>
END 
GO

GRANT EXECUTE ON [<Owner>].[<TableU>Select] TO [Public]
GO

--***********************************************************
--INSERT Stored Procedure for <Table> table
--***********************************************************
GO

USE <Database>
GO

IF EXISTS (SELECT sys.objects.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.name = '<TableU>Insert'  and sys.schemas.name = '<Owner>'  and sys.objects.type = 'P') 
DROP PROCEDURE [<Owner>].[<TableU>Insert] 
GO

CREATE PROCEDURE [<Owner>].[<TableU>Insert] 
<If Loop>
   <IsNotIdentity>
     <SF[,]>@<Field> <SQLType>
</If Loop>
     ,@ReturnValue int OUTPUT
AS
BEGIN

SET NOCOUNT ON

BEGIN TRANSACTION 

BEGIN TRY 

INSERT [<Owner>].[<Table>]
     (
<If Loop>
   <IsNotIdentity>
     <SF[,]>[<Field>]
</If Loop>
     )
VALUES
     (
<If Loop>
   <IsNotIdentity>
     <SF[,]>@<Field>
</If Loop>
     )

IF @@ROWCOUNT = 0
BEGIN
     ROLLBACK TRANSACTION
     SET @ReturnValue = 0
     RETURN @ReturnValue
END
ELSE
BEGIN
     COMMIT TRANSACTION
     SET @ReturnValue = 1
     RETURN @ReturnValue
END

END TRY

BEGIN CATCH
     DECLARE @Error_Message varchar(150)
     SET @Error_Message = ERROR_NUMBER() + ' ' + ERROR_MESSAGE()
     ROLLBACK TRANSACTION
     RAISERROR(@Error_Message,16,1)
      SET @ReturnValue = -1
      RETURN @ReturnValue
END CATCH

END
GO

GRANT EXECUTE ON [<Owner>].[<TableU>Insert] TO [Public]
GO

--***********************************************************
--UPDATE Stored Procedure for <Table> table
--***********************************************************
GO

USE <Database>
GO

IF EXISTS (SELECT sys.objects.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.name = '<TableU>Update'  and sys.schemas.name = '<Owner>'  and sys.objects.type = 'P') 
DROP PROCEDURE [<Owner>].[<TableU>Update]
GO

CREATE PROCEDURE [<Owner>].[<TableU>Update] 
<If Loop>
   <IsNotIdentity>
     <SF[,]>@New<Field> <SQLType>
</If Loop>
<If Loop>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
     ,@Old<Field> <SQLType>
</If Loop>
     ,@ReturnValue int OUTPUT
AS
BEGIN

SET NOCOUNT ON

BEGIN TRANSACTION 

BEGIN TRY 

UPDATE [<Owner>].[<Table>]
SET
<If Loop>
   <IsNotIdentity>
     <SF[,]>[<Field>] = @New<Field>
</If Loop>
WHERE
<If Loop>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
<SF[AND ]>[<Field>] = @Old<Field>
</If Loop>

IF @@ROWCOUNT = 0
BEGIN
     ROLLBACK TRANSACTION
     SET @ReturnValue = 0
     RETURN @ReturnValue
END
ELSE
BEGIN
     COMMIT TRANSACTION
     SET @ReturnValue = 1
     RETURN @ReturnValue
END

END TRY

BEGIN CATCH
     DECLARE @Error_Message varchar(150)
     SET @Error_Message = ERROR_NUMBER() + ' ' + ERROR_MESSAGE()
     ROLLBACK TRANSACTION
     RAISERROR(@Error_Message,16,1)
      SET @ReturnValue = -1
      RETURN @ReturnValue
END CATCH

END
GO

GRANT EXECUTE ON [<Owner>].[<TableU>Update] TO [Public]
GO


--***********************************************************
--DELETE Stored Procedure for <Table> table
--***********************************************************
GO

USE <Database>
GO

IF EXISTS (SELECT sys.objects.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.name = '<TableU>Delete'  and sys.schemas.name = '<Owner>'  and sys.objects.type = 'P') 
DROP PROCEDURE [<Owner>].[<TableU>Delete] 
GO

CREATE PROCEDURE [<Owner>].[<TableU>Delete]
<If Loop>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
     <SF[,]>@Old<Field> <SQLType>
</If Loop> 
     ,@ReturnValue int OUTPUT
AS
BEGIN

SET NOCOUNT ON

BEGIN TRANSACTION 

BEGIN TRY 

DELETE FROM [<Owner>].[<Table>]
WHERE
<If Loop>
   <IsNotText>
   <And>
   <IsNotNText>
   <And>
   <IsNotXML>
<SF[AND ]>[<Field>] = @Old<Field>
</If Loop>

IF @@ROWCOUNT = 0
BEGIN
     ROLLBACK TRANSACTION
     SET @ReturnValue = 0
     RETURN @ReturnValue
END
ELSE
BEGIN
     COMMIT TRANSACTION
     SET @ReturnValue = 1
     RETURN @ReturnValue
END

END TRY

BEGIN CATCH
     DECLARE @Error_Message varchar(150)
     SET @Error_Message = ERROR_NUMBER() + ' ' + ERROR_MESSAGE()
     ROLLBACK TRANSACTION
     RAISERROR(@Error_Message,16,1)
      SET @ReturnValue = -1
      RETURN @ReturnValue
END CATCH

END
GO

GRANT EXECUTE ON [<Owner>].[<TableU>Delete] TO [Public]
GO


